package controller;

import java.io.IOException;
import java.net.Inet4Address;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import entity.PageInfo;
import entity.Teacher;
import entity.User;
import util.JDBCUtil;

// Servlet就是一个普通的java类,一个类继承HttpServlet就是一个Servlet,
// JSP和Servlet都可以共享数据给外界访问，在浏览器都可以访问他们
@WebServlet("/teacher")
public class TeacherServlet extends HttpServlet{
	
	@Override
	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// 处理post请求乱码
		// req.setCharacterEncoding("UTF-8");
		
		// 哪个用户调用这段代码就是这个用户得到自己的Session
//		HttpSession session = req.getSession();
//		User user = (User) session.getAttribute("user");
//		if (user == null) {// 代表这个用户没有登录
//			// 转发操作：用在查找之后转发到jsp页面展示。其余的都可以用重定向
//			resp.sendRedirect(req.getContextPath() + "/login.jsp");
//			return;
//		}
		
		// http://localhost:8080/Java201202/teacher
		// http://localhost:8080/Java201202/teacher?method=selectAll
		// http://localhost:8080/Java201202/teacher?method=deleteById&id=2
		String method = req.getParameter("method");
		if (method == null || method.equals("")) {
			method = "selectByPage";
		}
		switch (method) {
		case "selectByPage":
			selectByPage(req, resp);
			break;
		case "selectAll":
			selectAll(req, resp);
			break;
		case "deleteById":
			deleteById(req, resp);
			break;
		case "selectById":
			selectById(req, resp);
			break;
		case "update":
			update(req, resp);
			break;

		default:
			break;
		}
	}

	private void selectByPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		System.out.println("TeacherServlet.selectByPage()");
		// http://localhost:8080/Java201202/teacher?method=selectAll
		// http://localhost:8080/Java201202/teacher?method=selectByPage&pageNo=1&pageSize=5
		// http://localhost:8080/Java201202/teacher?method=selectByPage&pageNo=2&pageSize=5
		// http://localhost:8080/Java201202/teacher?method=selectByPage&pageNo=3&pageSize=5
		String pageNoStr = req.getParameter("pageNo");
		String pageSizeStr = req.getParameter("pageSize");
		// 在分页时候如果用户忘了写pageNo和pageSize，默认是pageNo是第一页，pageSize是每页5条
		if (pageNoStr == null || pageNoStr.equals("")) {
			pageNoStr = "1";
		}
		if (pageSizeStr == null || pageSizeStr.equals("")) {
			pageSizeStr = "5";
		}
		int pageNo = Integer.parseInt(pageNoStr);
		int pageSize = Integer.parseInt(pageSizeStr);
		System.out.println("pageNo: " + pageNo);
		System.out.println("pageSize: " + pageSize);
		int offset = (pageNo - 1) * pageSize;
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<Teacher> list = new ArrayList<Teacher>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`salary` FROM `teacher` LIMIT ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				int salary = resultSet.getInt("salary");
				Teacher teacher = new Teacher(id, name, age, salary);
				list.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		for (Teacher teacher : list) {
			System.out.println(teacher);
		}
		
		
		
		// 在分页操作里面要做两条查询语句
		// 1、查询当前页的数据
		// 2、查询总的数量（拿到这个数量的目的是为了算总的页数）
		int totalCount = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT COUNT(*) FROM `teacher`";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				totalCount = resultSet.getInt("count(*)");
				System.out.println(totalCount);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		// 页面需要的多有信息现在都要封装到PageInfo
		PageInfo pageInfo = new PageInfo();
		pageInfo.setList(list);// 这个List集合是当前页的数据
		pageInfo.setPageNo(pageNo);
		pageInfo.setPageSize(pageSize);
		int totalPage = (int)Math.ceil((double)totalCount / pageSize);
		pageInfo.setTotalPage(totalPage);
		
		// req.setAttribute("list", list);
		//req.setAttribute("totalPage", totalPage);
		
		// 分页的本质还是在Servlet中做查询，然后将封装的结果PageInfo转发到jsp页面展示
		System.out.println(pageInfo);
		req.setAttribute("pageInfo", pageInfo);
		req.getRequestDispatcher("/teacher_list.jsp").forward(req, resp);
	}

	private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		System.out.println("TeacherServlet.update()");
		String id = req.getParameter("id");
		String name = req.getParameter("name");
		String age = req.getParameter("age");
		String salary = req.getParameter("salary");
		System.out.println("id: " + id);
		System.out.println("name: " + name);
		System.out.println("age: " + age);
		System.out.println("age: " + age);
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "UPDATE `teacher` SET `name`=?,`age`=?,`salary`=? WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setInt(2, Integer.parseInt(age));
			preparedStatement.setInt(3, Integer.parseInt(salary));
			preparedStatement.setInt(4, Integer.parseInt(id));
			System.out.println(preparedStatement);
			int count = preparedStatement.executeUpdate();
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		resp.sendRedirect(req.getContextPath() + "/teacher?method=selectByPage");
	}

	private void selectById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		System.out.println("TeacherServlet.selectById()");
		String selectId = req.getParameter("id");
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		Teacher teacher = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`salary` FROM `teacher` WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, Integer.parseInt(selectId));
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				int salary = resultSet.getInt("salary");
				teacher = new Teacher(id, name, age, salary);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		System.out.println(teacher);
		
		req.setAttribute("teacher", teacher);
		req.getRequestDispatcher("/teacher_update.jsp").forward(req, resp);
	}

	private void deleteById(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		System.out.println("TeacherServlet.deleteById()");
		// http://localhost:8080/Java201202/teacher?method=deleteById&id=2
		String id = req.getParameter("id");
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM `teacher` WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, Integer.parseInt(id));
			System.out.println(preparedStatement);
			int count = preparedStatement.executeUpdate();
			System.out.println("count:" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		// 重定向:告诉浏览器执行返回的请求
		// direction:方向 
		resp.sendRedirect(req.getContextPath() + "/teacher?method=selectByPage");
	}

	private void selectAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		System.out.println("TeacherServlet.selectAll()");
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<Teacher> list = new ArrayList<Teacher>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`age`,`salary` FROM `teacher`";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				int salary = resultSet.getInt("salary");
				Teacher teacher = new Teacher(id, name, age, salary);
				list.add(teacher);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		for (Teacher teacher : list) {
			System.out.println(teacher);
		}
		
		req.setAttribute("list", list);
		req.getRequestDispatcher("/teacher_list.jsp").forward(req, resp);
		
	}

	
	public static void main(String[] args) {
		int totalCount = 12;
		int pageSize = 5;
		System.out.println(totalCount / pageSize);// 2
		System.out.println((double)totalCount / pageSize);// 2.4
		System.out.println(Math.ceil((double)totalCount / pageSize));// 3.0
		System.out.println((int)Math.ceil((double)totalCount / pageSize));//3
	}
}
